using System;
using System.Collections.Generic;
using System.Text;

namespace Common
{
    /// <summary>
    /// help util to generate create sql script for oracle/sql server stored proc
    /// </summary>
    public class StoredProcUtil
    {
        #region Update Command
        /// <summary>
        /// 
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="storedProcName"></param>
        /// <param name="schemaName"></param>
        /// <param name="tblName"></param>
        /// <param name="fieldsToUpdate"></param>
        /// <param name="filters"></param>
        /// <returns></returns>
        public static string CreateStoredProcedureStatementForUpdate(
            DataProviderType provider,
            string storedProcName,
            string schemaName,
            string tblName,
            TableColumn[] fieldsToUpdate,
            FilterField[] filters)
        {
            System.Text.StringBuilder buffer = new StringBuilder();
            Dictionary<string, string> colName_ParamName_Mappings = new Dictionary<string, string>();
            Dictionary<string, string> colName_DataTypeString_Mappings = new Dictionary<string, string>();
            // System.Collections.Hashtable ht_ColNameParamName = new System.Collections.Hashtable();
            //  buffer.Append(StoredProcUtil.CreateDropStoredProcedureStatement(provider, storedProcName));
            buffer.Append("\n");

            List<TableColumn> fieldsList = new List<TableColumn>();
            foreach (TableColumn field in fieldsToUpdate)
            {
                bool isFilter = false;
                if (filters != null && filters.Length > 0)
                {
                    foreach (FilterField filter in filters)
                    {
                        if (filter.FieldName.ToLower() == field.ColumnName.ToLower())
                        {
                            isFilter = true;
                            break;
                        }
                    }
                }
                if (!isFilter)
                {
                    fieldsList.Add(field);
                    colName_ParamName_Mappings.Add(field.ColumnName, StoredProcUtil.GetParamNameFromColName(field.ColumnName));
                    FieldDataType fieldDataType = new FieldDataType();
                    fieldDataType.Provider = provider;
                    fieldDataType.DataType = field.DbType;
                    fieldDataType.Nullible = field.AllowNull;
                    fieldDataType.Scale = field.Scale;
                    fieldDataType.Size = field.Size;
                    if (provider == DataProviderType.Oracle)
                    {
                        colName_DataTypeString_Mappings.Add(field.ColumnName, FieldDataType.ToOracleFieldDataTypeStringWithoutSize(fieldDataType));
                    }
                    else
                    {
                        colName_DataTypeString_Mappings.Add(field.ColumnName, FieldDataType.ToMssqlFieldDataTypeString(fieldDataType));
                    }
                }
            }
            if (filters != null && filters.Length > 0)
            {
                foreach (FilterField filter in filters)
                {
                    colName_ParamName_Mappings.Add(filter.FieldName, StoredProcUtil.GetParamNameFromColName(filter.FieldName));
                    if (provider == DataProviderType.Oracle)
                    {
                        colName_DataTypeString_Mappings.Add(filter.FieldName, FieldDataType.ToOracleFieldDataTypeString(filter.FieldDbDataType));
                    }
                    else
                    {
                        colName_DataTypeString_Mappings.Add(filter.FieldName, FieldDataType.ToMssqlFieldDataTypeString(filter.FieldDbDataType));
                    }
                }
            }

            //for (int i = 0; i < fieldsList.Count; i++)
            //{
            //    if (!ht_ColNameParamName.Contains(fieldsToUpdate[i]))
            //    {
            //        string paramName = fieldsToUpdate[i].ColumnName;
            //        paramName = StoredProcUtil.GetParamNameFromColName(paramName);
            //        ht_ColNameParamName.Add(fieldsToUpdate[i], paramName);
            //    }
            //}
            if(provider==DataProviderType.Oracle)
            {
                buffer.Append("Create or Replace Procedure ");
            }
            else
            {
                buffer.Append("Create Procedure ");
            }
            buffer.Append(StoredProcUtil.GetProviderSpecificLeftEscape(provider)  + 
                storedProcName + 
                StoredProcUtil.GetProviderSpecificRightEscape(provider) + " \n");

            buffer.Append("(");
            foreach(string colName in colName_ParamName_Mappings.Keys)
            {
                if (!buffer.ToString().EndsWith("("))
                {
                    buffer.Append(", ");
                }
                buffer.Append("@"+colName_ParamName_Mappings[colName] + " " + 
                    colName_DataTypeString_Mappings[colName]);
            }

            buffer.Append(")\n");
            if (provider == DataProviderType.Oracle)
            {
                buffer.Append("IS \n");
            }
            else
            {
                buffer.Append("AS\n");
            }
            buffer.Append("BEGIN\n");
            buffer.Append("\tUpdate " +
                          (string.IsNullOrEmpty(schemaName)
                               ? string.Empty
                               : StoredProcUtil.GetProviderSpecificLeftEscape(provider) + schemaName +
                                 StoredProcUtil.GetProviderSpecificRightEscape(provider) + ".") +
                          StoredProcUtil.GetProviderSpecificLeftEscape(provider) + tblName +
                          StoredProcUtil.GetProviderSpecificRightEscape(provider) + "\n");
            buffer.Append("\tSET\n");

            string[] colNames = new string[colName_ParamName_Mappings.Count];
            colName_ParamName_Mappings.Keys.CopyTo(colNames, 0);

            for(int i=0;i<colNames.Length;i++)
            {
                string colName = colNames[i];
                buffer.Append("\t\t" + StoredProcUtil.GetProviderSpecificLeftEscape(provider) +
                              colName + StoredProcUtil.GetProviderSpecificRightEscape(provider) +
                              "=@" + colName_ParamName_Mappings[colName]);
                if (i < colNames.Length - 1)
                {
                    buffer.Append(",\n");
                }
                else
                {
                    buffer.Append("\n");
                }
            }

            if (filters != null && filters.Length > 0)
            {
                buffer.Append("\tWHERE\n");
                for (int i = 0; i < filters.Length; i++)
                {
                    buffer.Append("\t\t" + StoredProcUtil.GetProviderSpecificLeftEscape(provider)  + 
                        filters[i].FieldName + 
                        StoredProcUtil.GetProviderSpecificRightEscape(provider) + "=@" +
                        colName_ParamName_Mappings[filters[i].FieldName]);
                    if (i < filters.Length - 1)
                    {
                        buffer.Append(" and \n");
                    }
                }
                if (provider == DataProviderType.Oracle)
                    buffer.Append(";\n");
                else
                    buffer.Append("\n");
            }

            if (provider == DataProviderType.Oracle)
            {
                buffer.Append("END " + StoredProcUtil.GetProviderSpecificLeftEscape(provider) +
                              storedProcName +
                              StoredProcUtil.GetProviderSpecificRightEscape(provider) +
                              ";\n");
                buffer.Append("commit;\n");
            }
            else
            {
                buffer.Append("END\n");
            }
            return buffer.ToString();
        }
        #endregion

        #region Insert Command 
        /// <summary>
        /// 
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="storedProcName"></param>
        /// <param name="schemaName"></param>
        /// <param name="tblName"></param>
        /// <param name="fields"></param>
        /// <returns></returns>
        public static string CreateStoredProcedureStatementForInsert(
            DataProviderType provider, 
            string storedProcName,
            string schemaName,
            string tblName,
            TableColumn[] fields)
        {
            System.Text.StringBuilder buffer = new StringBuilder();
            // buffer.Append(StoredProcUtil.CreateDropStoredProcedureStatement(provider, storedProcName));
            buffer.Append("\n");
            if(provider==DataProviderType.Oracle)
            {
                buffer.Append("Create or Replace Procedure ");
            }
            else
            {
                buffer.Append("Create Procedure ");
            }
            buffer.Append(
                StoredProcUtil.GetProviderSpecificLeftEscape(provider) + 
                storedProcName + 
                StoredProcUtil.GetProviderSpecificRightEscape(provider) + 
                " \n");
            System.Collections.Hashtable ht_ColNameParamName = new System.Collections.Hashtable();
            buffer.Append("(");
            for (int i = 0; i < fields.Length; i++)
            {
                if (!ht_ColNameParamName.Contains(fields[i]))
                {
                    string paramName = fields[i].ColumnName;
                    paramName = StoredProcUtil.GetParamNameFromColName(paramName);
                    ht_ColNameParamName.Add(fields[i], paramName);
                    if(provider==DataProviderType.Oracle)
                    {
                        buffer.Append(paramName + " " +
                            FieldDataType.ToOracleFieldDataTypeStringWithoutSize(fields[i].GetFieldDbType(provider)));    
                    }
                    else
                    {
                        buffer.Append("@" + paramName + " " +
                                      FieldDataType.ToMssqlFieldDataTypeString(fields[i].GetFieldDbType(provider)));
                    }
                    if (i < fields.Length - 1)
                    {
                        buffer.Append(",");
                    }
                }
            }
            buffer.Append(")\n");
            if(provider==DataProviderType.Oracle)
            {
                buffer.Append("IS\n");
            }
            else
            {
                buffer.Append("AS\n");
            }
            buffer.Append("BEGIN\n");
            if (string.IsNullOrEmpty(schemaName))
            {
                buffer.Append("\tINSERT INTO " + 
                    StoredProcUtil.GetProviderSpecificLeftEscape(provider) +
                    tblName + 
                    StoredProcUtil.GetProviderSpecificRightEscape(provider) + 
                    "\n");
            }
            else
            {
                buffer.Append("\tINSERT INTO " + 
                    StoredProcUtil.GetProviderSpecificLeftEscape(provider) +
                    schemaName + 
                    StoredProcUtil.GetProviderSpecificRightEscape(provider) + 
                    "." +
                    StoredProcUtil.GetProviderSpecificLeftEscape(provider) + 
                    tblName + 
                    StoredProcUtil.GetProviderSpecificRightEscape(provider) + "\n");
            }
            buffer.Append("\t(\n");
            for (int i = 0; i < fields.Length; i++)
            {
                buffer.Append("\t\t" + 
                    StoredProcUtil.GetProviderSpecificLeftEscape(provider)  + 
                    fields[i].ColumnName + 
                    StoredProcUtil.GetProviderSpecificRightEscape(provider) + 
                    "");
                if (i < fields.Length - 1)
                {
                    buffer.Append(",\n");
                }
                else
                {
                    buffer.Append("\n");
                }
            }
            buffer.Append("\t) values (\n");
            for (int i = 0; i < fields.Length; i++)
            {
                if(provider==DataProviderType.Oracle)
                {
                    buffer.Append((string) ht_ColNameParamName[fields[i]]);
                }
                else
                {
                    buffer.Append("@" + (string) ht_ColNameParamName[fields[i]]);    
                }
                
                if (i < fields.Length - 1)
                {
                    buffer.Append(",\n");
                }
                else
                {
                    buffer.Append("\n");
                }
            }

            if(provider==DataProviderType.Oracle)
            {
                buffer.Append(");\ncommit;\n");
            }
            else
            {
                buffer.Append(")\n");
            }

            if (provider == DataProviderType.Oracle)
            {
                buffer.Append("END " +
                              StoredProcUtil.GetProviderSpecificLeftEscape(provider) +
                              storedProcName +
                              StoredProcUtil.GetProviderSpecificRightEscape(provider) +
                              ";\n");
            }
            else
            {
                buffer.Append("END\n");
            }
            return buffer.ToString();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="storedProcName"></param>
        /// <param name="schemaName"></param>
        /// <param name="tblName"></param>
        /// <param name="fields"></param>
        /// <param name="identityPkField"></param>
        /// <returns></returns>
        public static string CreateStoredProcedureStatementForIdentityInsert(
            DataProviderType provider,
            string storedProcName,
            string schemaName,
            string tblName,
            TableColumn[] fields, 
            string identityPkField)
        {
            List<string> nonPkColNames=new List<string>();
            foreach(TableColumn col in fields)
            {
                if(col.ColumnName.ToLower()!=identityPkField.ToLower())
                {
                    nonPkColNames.Add(col.ColumnName);
                }
            }

            System.Text.StringBuilder buffer = new StringBuilder();
            // buffer.Append(StoredProcUtil.CreateDropStoredProcedureStatement(provider, storedProcName));
            buffer.Append("\n");

            buffer.Append("Create Procedure " +
                StoredProcUtil.GetProviderSpecificLeftEscape(provider) +
                storedProcName +
                StoredProcUtil.GetProviderSpecificRightEscape(provider) +
                " \n");
            Dictionary<string,string> colNameParamName = new Dictionary<string, string>();
            buffer.Append("(");
            for (int i = 0; i < fields.Length; i++)
            {
                if (!colNameParamName.ContainsKey(fields[i].ColumnName))
                {
                    string paramName = fields[i].ColumnName;
                    paramName = StoredProcUtil.GetParamNameFromColName(paramName);
                    colNameParamName.Add(fields[i].ColumnName, paramName);
                    buffer.Append("@" + paramName + " " +
                        FieldDataType.ToMssqlFieldDataTypeString(fields[i].GetFieldDbType(provider)));
                    if(fields[i].ColumnName.ToLower()==identityPkField.ToLower())
                    {
                        buffer.Append(" output");
                    }
                    if (i < fields.Length - 1)
                    {
                        buffer.Append(",");
                    }
                }
            }
            buffer.Append(")\n");

            buffer.Append("AS\n");
            buffer.Append("BEGIN\n");
            if (string.IsNullOrEmpty(schemaName))
            {
                buffer.Append("\tINSERT INTO " +
                    StoredProcUtil.GetProviderSpecificLeftEscape(provider) +
                    tblName +
                    StoredProcUtil.GetProviderSpecificRightEscape(provider) +
                    "\n");
            }
            else
            {
                buffer.Append("\tINSERT INTO " +
                    StoredProcUtil.GetProviderSpecificLeftEscape(provider) +
                    schemaName +
                    StoredProcUtil.GetProviderSpecificRightEscape(provider) +
                    "." +
                    StoredProcUtil.GetProviderSpecificLeftEscape(provider) +
                    tblName +
                    StoredProcUtil.GetProviderSpecificRightEscape(provider) + "\n");
            }
            buffer.Append("\t(\n");
            for (int i = 0; i < nonPkColNames.Count; i++)
            {
                buffer.Append("\t\t" +
                              StoredProcUtil.GetProviderSpecificLeftEscape(provider) +
                              nonPkColNames[i] +
                              StoredProcUtil.GetProviderSpecificRightEscape(provider) +
                              "");

                if (i < nonPkColNames.Count - 1)
                {
                    buffer.Append(",\n");
                }
                else
                {
                    buffer.Append("\n");
                }
            }
            buffer.Append("\t) values (\n");
            for (int i = 0; i < nonPkColNames.Count; i++)
            {
                buffer.Append("@" + colNameParamName[nonPkColNames[i]]);
                if (i < nonPkColNames.Count - 1)
                {
                    buffer.Append(",\n");
                }
                else
                {
                    buffer.Append("\n");
                }

            }
            buffer.Append(")\n");
            buffer.Append("set @" + colNameParamName[identityPkField] + "=(select max ([" +
                              identityPkField + "]) from [" +
                              (string.IsNullOrEmpty(schemaName) ? tblName : schemaName + "." + tblName) +
                              "])\n");

            buffer.Append("END\n");
            return buffer.ToString();
        } 
        #endregion

        #region Delete Command
        /// <summary>
        /// 
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="storedProcName"></param>
        /// <param name="schemaName"></param>
        /// <param name="tblName"></param>
        /// <param name="filterByFieldName"></param>
        /// <param name="filters"></param>
        /// <returns></returns>
        public static string CreateStoredProcedureStatementForDelete(
            DataProviderType provider, 
            string storedProcName,
            string schemaName,
            string tblName,
            string filterByFieldName,
            FilterField[] filters)
        {
            System.Text.StringBuilder buffer = new StringBuilder();
            buffer.Append(StoredProcUtil.CreateDropStoredProcedureStatement(provider, storedProcName));
            buffer.Append("\n");

            buffer.Append("Create Procedure " + StoredProcUtil.GetProviderSpecificLeftEscape(provider)  + storedProcName + StoredProcUtil.GetProviderSpecificRightEscape(provider) + " \n");
            if (filters != null && filters.Length > 0)
            {
                buffer.Append("(");
                for (int i = 0; i < filters.Length; i++)
                {
                    buffer.Append("@" + StoredProcUtil.GetParamNameFromColName(filters[i].FieldName) +
                        " " + FieldDataType.ToString(filters[i].FieldDbDataType));
                    if (i < filters.Length - 1)
                    {
                        buffer.Append(",\n");
                    }
                }
                buffer.Append(")\n");
            }

            buffer.Append("AS\n");
            buffer.Append("BEGIN\n");
            buffer.Append("\tDELETE FROM \n");
            buffer.Append("\t\t" + StoredProcUtil.GetProviderSpecificLeftEscape(provider)  +
                (string.IsNullOrEmpty(schemaName) ? string.Empty : "" + StoredProcUtil.GetProviderSpecificLeftEscape(provider)  + schemaName + StoredProcUtil.GetProviderSpecificRightEscape(provider) + ".") +
                "" + StoredProcUtil.GetProviderSpecificLeftEscape(provider)  + tblName + StoredProcUtil.GetProviderSpecificRightEscape(provider) + "\n");

            if (filters != null && filters.Length > 0)
            {
                buffer.Append("\tWHERE\n");
                for (int i = 0; i < filters.Length; i++)
                {
                    buffer.Append("\t\t" + StoredProcUtil.GetProviderSpecificLeftEscape(provider)  + filters[i].FieldName + StoredProcUtil.GetProviderSpecificRightEscape(provider) + "=@" +
                        StoredProcUtil.GetParamNameFromColName(filters[i].FieldName));
                    if (i < filters.Length - 1)
                    {
                        buffer.Append(" and \n");
                    }
                }
                buffer.Append("\n\n");
            }

            buffer.Append("END\n");
            return buffer.ToString();
        }
        #endregion

        #region util
        /// <summary>
        /// 
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="storedProcName"></param>
        /// <returns></returns>
        public static string CreateDropStoredProcedureStatement(DataProviderType provider, string storedProcName)
        {
            System.Text.StringBuilder buffer = new StringBuilder();
            buffer.Append("IF EXISTS (SELECT * FROM sys.objects WHERE name = '" + 
                storedProcName + "')\n");
            buffer.Append("BEGIN\n");
            buffer.Append("\t" + "DROP  Procedure " + StoredProcUtil.GetProviderSpecificLeftEscape(provider)  + storedProcName + StoredProcUtil.GetProviderSpecificRightEscape(provider) + "\n");
            buffer.Append("END\n");

            return buffer.ToString();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="objName"></param>
        /// <returns></returns>
        public static string TidyObjectName(string objName)
        {
            while (objName.IndexOf(" ") >= 0)
            {
                objName = objName.Replace(" ", "");
            }
            return objName;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="colName"></param>
        /// <returns></returns>
        public static string GetParamNameFromColName(string colName)
        {
            return StoredProcUtil.TidyObjectName(colName);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="provider"></param>
        /// <returns></returns>
        public static string GetProviderSpecificLeftEscape(DataProviderType provider)
        {
            if (provider == DataProviderType.MSSQL)
            {
                return "[";
            }
            else if (provider == DataProviderType.Oracle)
            {
                return "";
            }
            else
            {
                return "";
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="provider"></param>
        /// <returns></returns>
        public static string GetProviderSpecificRightEscape(DataProviderType provider)
        {
            if (provider == DataProviderType.MSSQL)
            {
                return "]";
            }
            else if (provider == DataProviderType.Oracle)
            {
                return "";
            }
            else
            {
                return "";
            }
        }
        #endregion
    }
}
